﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class Orders : System.Web.UI.Page
{
    SqlDataAdapter da;
    SqlConnection con;
    DataTable dt;
    DataTable dtOrderDetails;
    DataView dv;
    int code = 1;

    private void loadOrders()
    {
        con = new SqlConnection();
        con.ConnectionString = WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        switch (code)
        {
            case 1:
                cmd.CommandText = "sp_SelectFromtblORders";
                break;
            case 2:
                cmd.CommandText = "sp_SearchByCustomer";
                SqlParameter para = new SqlParameter("@customer", SqlDbType.NVarChar, 40);
                para.Value = ddlFilter.SelectedValue.ToString();
                cmd.Parameters.Add(para);
                break;
            case 3:
                cmd.CommandText = "sp_SearchByDate";
                SqlParameter param = new SqlParameter("@dateFrom", SqlDbType.DateTime);
                param.Value = txtDateSearchFrom.Value.ToString();
                cmd.Parameters.Add(param);
                param = new SqlParameter("@dateTo", SqlDbType.DateTime);
                param.Value = txtDateSearchTo.Value.ToString();
                cmd.Parameters.Add(param);
                break;
            case 4:
                cmd.CommandText = "sp_SearchByProduct";
                SqlParameter paramenter = new SqlParameter("@product", SqlDbType.NVarChar);
                paramenter.Size = 40;
                paramenter.Value = ddlFilter.Text;
                cmd.Parameters.Add(paramenter);
                //gvOrders.Columns[17].Visible = true;
                break;
            default:
                break;
        }

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        dt = new DataTable("Orders");
        // dtOrder = new DataTable("Order");
        da.Fill(dt);
        gvOrders.AutoGenerateSelectButton = true;
        gvOrders.AllowPaging = true;
        gvOrders.PageSize = 8;
        gvOrders.DataSource = dt;
        gvOrders.DataBind();


    }








    private void loadComboBox()
    {
        con = new SqlConnection();
        con.ConnectionString =
            WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        //load customer

        da = new SqlDataAdapter("Select * from Sales.Customers", con);
        dt = new DataTable("Customers");
        da.Fill(dt);
        ddlCustomer.DataSource = dt;
        ddlCustomer.DataTextField = "companyname";
        ddlCustomer.DataValueField = "custid";
        ddlCustomer.DataBind();
        //ddlCustomer.Items.Add("----Please Choose-----");

        //load employee
        da = new SqlDataAdapter("select * from HR.Employees", con);
        dt = new DataTable("Employees");
        da.Fill(dt);
        ddlEmployee.DataSource = dt;
        ddlEmployee.DataTextField = "lastname";
        ddlEmployee.DataValueField = "empid";
        ddlEmployee.DataBind();
        //load shipper
        da = new SqlDataAdapter("Select * from Sales.Shippers", con);
        dt = new DataTable("Shipper");
        da.Fill(dt);
        ddlShipper.DataSource = dt;
        ddlShipper.DataTextField = "companyname";
        ddlShipper.DataValueField = "shipperid";
        ddlShipper.DataBind();


    }

    private void clearField()
    {
        txtOrderID.Text = "";
        ddlCustomer.SelectedIndex = -1;
        ddlEmployee.SelectedIndex = -1;
        txtOrderDate.Text = "";
        txtRequiredDate.Text = "";
        txtShippedDate.Text = "";
        txtShipAddress.Text = "";
        ddlShipper.SelectedIndex = -1;
        txtFreight.Text = "";
        txtShipName.Text = "";
        txtShipAddress.Text = "";
        txtShipCity.Text = "";
        txtShipRegion.Text = "";
        txtShipPostalCost.Text = "";
        ddlShipCountry.SelectedIndex = -1;
    }

    private void addOrders()
    {
        con = new SqlConnection();
        con.ConnectionString =
            WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        con.Open();
        try
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_InsertTotblOrders";
            SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
            if (ddlCustomer.SelectedIndex == -1)
            {
                param.Value = DBNull.Value;
            }
            else
                param.Value = ddlCustomer.SelectedValue.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = ddlEmployee.SelectedValue.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@orderdate", SqlDbType.DateTime);
            param.Value = txtOrderDate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@requireddate", SqlDbType.DateTime);
            param.Value = txtRequiredDate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
            if (txtShippedDate.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtShippedDate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipperid", SqlDbType.Int);
            param.Value = ddlShipper.SelectedValue.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@freight", SqlDbType.Money);
            param.Value = txtFreight.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtShipName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipaddress", SqlDbType.NVarChar);
            param.Size = 60;
            param.Value = txtShipAddress.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcity", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = txtShipCity.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipregion", SqlDbType.NVarChar);
            if (txtShipRegion.Text.Length == 0)
            {
                param.Size = 15;
                param.Value = DBNull.Value;
            }
            else
            {
                param.Size = 15;
                param.Value = txtShipRegion.Text.Trim();
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar);
            if (txtShipPostalCost.Text.Length == 0)
            {
                param.Size = 10;
                param.Value = DBNull.Value;
            }
            else
            {
                param.Size = 10;
                param.Value = txtShipPostalCost.Text.Trim();
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcountry", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = ddlShipCountry.Text.Trim();
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
        finally
        {
            con.Close();
        }
    }

    private void updateOrders()
    {
        //if (gvOrders.SelectedRows.Count > 0)
        //{
        con = new SqlConnection();
        con.ConnectionString =
       WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        con.Open();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_UpdatetblOrders";
            SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
            param.Value = txtOrderID.Text.Trim();
            cmd.Parameters.Add(param);
            param = new SqlParameter("@custid", SqlDbType.Int);

            if (ddlCustomer.SelectedIndex == -1)
            {
                param.Value = DBNull.Value;
            }
            else
                param.Value = ddlCustomer.SelectedValue.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = ddlEmployee.SelectedValue.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@orderdate", SqlDbType.DateTime);
            param.Value = txtOrderDate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@requireddate", SqlDbType.DateTime);
            param.Value = txtRequiredDate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
            if (txtShippedDate.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtShippedDate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipperid", SqlDbType.Int);
            param.Value = ddlShipper.SelectedValue.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@freight", SqlDbType.Money);
            param.Value = txtFreight.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtShipName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipaddress", SqlDbType.NVarChar);
            param.Size = 60;
            param.Value = txtShipAddress.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcity", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = txtShipCity.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipregion", SqlDbType.NVarChar);
            if (txtShipRegion.Text.Length == 0)
            {
                param.Size = 15;
                param.Value = DBNull.Value;
            }
            else
            {
                param.Size = 15;
                param.Value = txtShipRegion.Text.Trim();
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar);
            if (txtShipPostalCost.Text.Length == 0)
            {
                param.Size = 10;
                param.Value = DBNull.Value;
            }
            else
            {
                param.Size = 10;
                param.Value = txtShipPostalCost.Text.Trim();
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcountry", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = ddlShipCountry.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {

            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
        finally
        {
            con.Close();
        }
        // }
    }

    private void deleteOrder()
    {
        con = new SqlConnection();
        con.ConnectionString =
        WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        con.Open();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_DeleteOrders";
            SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
            param.Value = txtOrderID.Text;
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));

        }
        finally
        {
            con.Close();
        }
    }


    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.IsPostBack == false)
        {
            loadOrders();
            loadComboBox();
            clearField();
            ViewState["search"] = false;
            ViewState["FilterCustomer"] = false;
            ViewState["FilterProduct"] = false;
        }
    }
    protected void gvOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        loadOrders();
        gvOrders.PageIndex = e.NewPageIndex;
        gvOrders.DataBind();
        if ((bool)ViewState["search"] == true)
        {
            code = 3;
            loadOrders();
        }

        if ((bool)ViewState["FilterProduct"] == true)
        {
            code = 4;
            loadOrders();
        }

        if ((bool)ViewState["FilterCustomer"] == true)
        {
            code = 2;
            loadOrders();
        }
    }
    protected void gvOrders_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridViewRow r = gvOrders.SelectedRow;
        txtOrderID.Text = Server.HtmlDecode(r.Cells[1].Text);
        ddlCustomer.Text = r.Cells[2].Text;
        ddlEmployee.Text = r.Cells[3].Text;
        txtOrderDate.Text = Server.HtmlDecode(r.Cells[4].Text.Substring(0, 10));
        txtRequiredDate.Text = Server.HtmlDecode(r.Cells[5].Text.Substring(0, 10));
        if (Server.HtmlDecode(r.Cells[6].Text).Trim().Length == 0)
        {
            txtShippedDate.Text = "";
        }
        else
            txtShippedDate.Text = Server.HtmlDecode(r.Cells[6].Text.Substring(0, 10));
        ddlShipper.Text = r.Cells[7].Text;
        txtFreight.Text = Server.HtmlDecode(r.Cells[8].Text);
        txtShipName.Text = Server.HtmlDecode(r.Cells[9].Text);
        txtShipAddress.Text = Server.HtmlDecode(r.Cells[10].Text);
        txtShipCity.Text = Server.HtmlDecode(r.Cells[11].Text);
        txtShipRegion.Text = Server.HtmlDecode(r.Cells[12].Text);
        txtShipPostalCost.Text = Server.HtmlDecode(r.Cells[13].Text);
        ddlShipCountry.Text = r.Cells[14].Text;
        btnAdd.Enabled = false;
        btnDelete.Enabled = true;
        btnUpdate.Enabled = true;
        Session["getID"] = txtOrderID.Text;
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        btnAdd.Enabled = true;
        btnDelete.Enabled = false;
        btnUpdate.Enabled = false;
        loadComboBox();
        clearField();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        addOrders();
        loadOrders();
        loadComboBox();
        Response.Write("<script>alert('Done!');</script>");
        clearField();
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        updateOrders();
        loadOrders();
        clearField();
        Response.Write("<script>alert('Done!');</script>");
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteOrder();
        clearField();
        loadOrders();
        loadComboBox();
        Response.Write("<script>alert('Done!');</script>");
    }
    protected void txtOrderDate_TextChanged(object sender, EventArgs e)
    {

    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        ViewState["search"] = true;
        code = 3;
        loadOrders();

    }
    protected void rdlFilter_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (rdlFilter.SelectedItem.Text == "Product")
        {
            //load Filter
            con = new SqlConnection();
            con.ConnectionString =
            WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;

            da = new SqlDataAdapter("select * from Production.Products", con);
            dt = new DataTable("FilterProduct");
            da.Fill(dt);
            ddlFilter.DataSource = dt;
            ddlFilter.DataTextField = "productname";
            ddlFilter.DataValueField = "productname";
            ddlFilter.DataBind();
        }
        else ddlFilter.DataSource = null;
        if (rdlFilter.SelectedItem.Text == "Customer")
        {
            //load Filter
            con = new SqlConnection();
            con.ConnectionString = WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            da = new SqlDataAdapter("Select * from Sales.Customers", con);
            dt = new DataTable("FilterCustomer");
            da.Fill(dt);
            ddlFilter.DataSource = dt;
            ddlFilter.DataTextField = "companyname";
            ddlFilter.DataValueField = "companyname";
            ddlFilter.DataBind();
        }
        else ddlFilter.DataSource = null;
    }
    protected void btnFilter_Click(object sender, EventArgs e)
    {

        if (rdlFilter.SelectedValue.ToString().Equals("Customer"))
        {
            ViewState["FilterCustomer"] = true;


            code = 2;
            loadOrders();
            //dv = new DataView(dtOrder);
            //dv.RowFilter = string.Format("Customer like'{0}'", ddlFilter.SelectedValue.ToString());
            //gvOrders.DataSource = dv;
        }
        if (rdlFilter.SelectedValue.ToString().Equals("Product"))
        {
            ViewState["FilterProduct"] = true;
            code = 4;
            loadOrders();
        }
        if (!rdlFilter.SelectedValue.ToString().Equals("Product") && !rdlFilter.SelectedValue.ToString().Equals("Customer"))
        {
            Response.Write("<script>alert('Please choose option to filter!');</script>");

        }
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        ViewState["search"] = false;
        ViewState["FilterProduct"] = false;
        ViewState["FilterCustomer"] = false;
        code = 1;
        loadOrders();

    }
    //protected void gvOrderDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
    //{

    //}
    //protected void gvOrderDetails_SelectedIndexChanged(object sender, EventArgs e)
    //{

    //}



    protected void btnGoToOrderDetails_Click(object sender, EventArgs e)
    {
        Response.Redirect("OrderDetails.aspx");
    }
}
